﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
//using Microsoft.Office.Interop.Excel;
//using Microsoft.Office.Interop.Excel;
namespace MigrationTool
{
    public partial class MainForm : Form
    {

        public delegate void Proxy(string msg, Color? fontcolor = null);
        public delegate void ProcessInitSet(int max, string txt);
        public delegate void ProcessSet(int value, string txt = null);
        public delegate void ProcessReSet();
        public delegate void BtnProxy(System.Windows.Forms.Button btn,bool b);

        private SelectedTable selectedtable = new SelectedTable();

        private System.Threading.Timer showDelay;// = new System.Threading.Timer();
        private System.Threading.Timer hideDelay;

        private FileSystemWatcher fileWatcher;
        //private LogForm logForm;
        private CancellationTokenSource cts;

        private DateTime lastModifiedTime;

        public static DateTime Now;

        

        public static DateTime Today;
        private List<string> tableNames;
       // private Microsoft.Office.Interop.Excel.Application app = null;
        //private object MainForm_OnClosed;
        public MainForm()
        {
            InitializeComponent();
            this.selectedtable.Tag = this;
            //logForm = new LogForm();
        }

        public void setTargetTableNames(List<string> tableNames)
        {
            this.tableNames = tableNames;
        }

        private List<string> AllSheet(string fileFullName)
        {
            FileInfo file = new FileInfo(fileFullName);
            
            using (Stream stream = file.Open(FileMode.Open,FileAccess.Read,FileShare.ReadWrite))
            {
                IWorkbook book = null;
                if (file.Extension.ToLower() == ".xlsx")
                    book = new XSSFWorkbook(stream);
                else
                    book = new HSSFWorkbook(stream);
                List<string> tables = new List<string>();
                for (int i = 0; i < book.NumberOfSheets; i++)
                {
                    tables.Add(book.GetSheetAt(i).SheetName+"$");
                }
                return tables;
            }
        }

        private void FileBrowser_Click(object sender, EventArgs e)
        {
            OpenFileDialog open = new OpenFileDialog();
            open.Filter = "数据文件(*.xls,*.xlsx)|*.xls;*.xlsx";
            if (open.ShowDialog()==DialogResult.OK)
            {
                if (fileWatcher != null)
                    fileWatcher.Dispose();
                txtExcelFile.Text = open.FileName;
                FileInfo exfl = new FileInfo(txtExcelFile.Text);
                try
                {
                    tableNames =AllSheet(txtExcelFile.Text);
                    this.selectedtable.SetListData(tableNames);
                    lastModifiedTime = exfl.LastWriteTime;
                    fileWatcher = new FileSystemWatcher(exfl.Directory.FullName);
                    fileWatcher.Filter = exfl.Name;
                    fileWatcher.IncludeSubdirectories = false;
                    fileWatcher.NotifyFilter = NotifyFilters.Attributes ;
                    fileWatcher.Changed += fileWatcher_Changed;
                    fileWatcher.EnableRaisingEvents = true;
                }
                catch(Exception ex)
                {
                    Appendlog("读取文件失败:"+ex.Message,Color.DarkRed);
                }


            }
        }

        void fileWatcher_Changed(object sender, FileSystemEventArgs e)
        {

                FileInfo exfl = new FileInfo(e.FullPath);
                if (lastModifiedTime == exfl.LastWriteTime)
                    return;
                lastModifiedTime = exfl.LastWriteTime;
                //Appendlog("文件[" + e.FullPath + "]已修改.", Color.DarkRed);
                try
                {
                    if (this.selectedtable.SetListData(AllSheet(e.FullPath)))
                    {
                        Appendlog("文件[" + e.FullPath + "]中的表个数或者名称已经改变.请确认是否需要重新选择导入对象!", Color.DarkOrange);
                        
                    }
                        
                }
                catch (Exception ex)
                {
                    Appendlog("读取文件失败:" + ex.Message, Color.DarkRed);
                }
        }
        protected override void OnClosed(EventArgs e)
        {
            //Close();
            //if (app != null)
            //{
                
            //    app.Quit();
            //}
            base.OnClosed(e);
        }
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            if (!LoadConfig())
            {
                txtServer.Text = "localhost";
                //txtExcelFile.Text = AppDomain.CurrentDomain.BaseDirectory + "WSS_Data.xlsx";
                this.cmbAuthType.SelectedIndex = 1;
            }

            //System.Drawing.Point p = new System.Drawing.Point();

            //p.X = this.Location.X + this.Height; ;
            //p.Y = this.Location.Y;
            //Location = p;
            //Show(null);
            //Left = this.Left;
            //Top = this.Top + this.Height;
        }
        //private void TemplateBrowser_Click(object sender, EventArgs e)
        //{
        //    FolderBrowserDialog folder = new FolderBrowserDialog();
        //    if (folder.ShowDialog() == DialogResult.OK)
        //    {
        //        txtServer.Text = folder.SelectedPath + @"\";
        //    }
        //}

        public void SetButtonEnable(System.Windows.Forms.Button btn,bool b)
        {

            this.Invoke((BtnProxy)delegate(System.Windows.Forms.Button butn, bool bl)
            {

                butn.Enabled = bl;

            }, btn, b);
            //if (this.InvokeRequired)//等待异步
            //{

            //    BtnProxy fc = new BtnProxy(SetButtonEnable);

            //    this.Invoke(fc, btn,b);//通过代理调用刷新方法
            //}
            //else
            //{
            //    btn.Enabled = b;
            //}
        }

        private bool LoadConfig()
        {
            bool rtn = false;
            string filename = AppDomain.CurrentDomain.BaseDirectory + Environment.UserName + ".config";
            if (File.Exists(filename))
            {
                FileStream fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + Environment.UserName + ".config", FileMode.Open, FileAccess.Read);
                try
                {
                    BinaryFormatter bf = new BinaryFormatter();
                    Config c=(Config)bf.Deserialize(fs);//.Serialize(fs, config);
                    if (c != null)
                    {
                        //this.txtExcelFile.Text=c.FileName;
                        this.txtServer.Text = c.ServerName;
                        this.txtDatabase.Text=c.Db;
                        this.cmbAuthType.SelectedIndex = c.AuthType;
                        if (c.AuthType != 0)
                        {
                            this.txtUser.Text = c.User;
                            this.txtPass.Text = c.Pass;
                        }
                        this.chkDel.Checked = c.DelFirst;
                        this.chkContinue.Checked = c.ContinueOnErr;
                        rtn = true;
                    }
                }
                catch (Exception)
                {


                }
                finally
                {
                    fs.Close();
                }
            }
            return rtn;
        }

        private void SaveConfig()
        {
            Config config = null;
            this.Invoke((System.Action)delegate()
            {
                config = new Config()
                {
                    FileName=this.txtExcelFile.Text,
                    ServerName=this.txtServer.Text,
                    Db=this.txtDatabase.Text,
                    AuthType=this.cmbAuthType.SelectedIndex,
                    
                    User=this.txtUser.Text,
                    Pass=this.txtPass.Text,
                    DelFirst=this.chkDel.Checked,
                    ContinueOnErr=this.chkContinue.Checked


                    
                };


            });
            FileStream fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + Environment.UserName + ".config", FileMode.Create, FileAccess.Write);
            try
            {
                BinaryFormatter bf = new BinaryFormatter();
                bf.Serialize(fs, config);
            }catch(Exception e)
            {

            }
            finally
            {
                fs.Close();
            }


        }


        private void btnCreator_Click(object sender, EventArgs e)
        {
            Now = DateTime.Now;
            Today = DateTime.Today;
            this.Appendlog();
            if (string.IsNullOrEmpty(txtExcelFile.Text) || !System.IO.File.Exists(txtExcelFile.Text))
            {
                this.Appendlog("数据文件不存在:\n" + txtExcelFile.Text, Color.DarkRed);
                return;
            }
            if (string.IsNullOrWhiteSpace(txtServer.Text))
            {
                this.Appendlog("请输入数据库服务器地址.", Color.DarkRed);
                txtServer.Focus();
                return;
            }
            if (string.IsNullOrWhiteSpace(txtDatabase.Text))
            {
                this.Appendlog("请输入数据库名称.", Color.DarkRed);
                txtDatabase.Focus();
                return;
            }
            if (cmbAuthType.SelectedIndex != 0)
            {
                if (string.IsNullOrWhiteSpace(txtUser.Text))
                {
                    this.Appendlog("请输入用户名.", Color.DarkRed);
                    txtUser.Focus();
                    return;
                }
                if (string.IsNullOrWhiteSpace(txtPass.Text))
                {
                    this.Appendlog("请输入密码.", Color.DarkRed);
                    txtPass.Focus();
                    return;
                }
            }

            FileInfo exfl = new FileInfo(txtExcelFile.Text);
            System.Data.OleDb.OleDbConnection excelfile;
            if (exfl.Extension.ToLower() == ".xlsx")
            {
                excelfile = new System.Data.OleDb.OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0", txtExcelFile.Text));
            }
            else
            {
                excelfile = new System.Data.OleDb.OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", txtExcelFile.Text));
            }

            //this.Tag = tableNames;
            SqlConnection connection;
            if (this.cmbAuthType.SelectedIndex == 0)//windows认证
            {
                connection = new SqlConnection(string.Format("Server={0};Integrated Security=SSPI;Database={1}", this.txtServer.Text,this.txtDatabase.Text));
            }
            else
            {
                connection = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=False;User ID={2};Password={3}",txtServer.Text,txtDatabase.Text,txtUser.Text,txtPass.Text));
            }

                
            SetButtonEnable(this.btnCreator,false);
            Task.Run(async () =>
            {
                //if(app ==null)
                //    app = new Microsoft.Office.Interop.Excel.Application();
                //Microsoft.Office.Interop.Excel.Workbook book=null;
                try
                {
                    //book=app.Workbooks.Open(txtExcelFile.Text,ReadOnly:true);
                    fileWatcher.EnableRaisingEvents = false;
                    connection.Open();
                    excelfile.Open();
                    ///保存配置
                    SaveConfig();
                    //excelfile.
                    MigrationToolLogic logic = new MigrationToolLogic(excelfile, connection);
                    logic.TemplateFolderPath = txtServer.Text;
                    logic.OutputFolder = new FileInfo(txtExcelFile.Text).Directory.FullName + @"\output";
                    logic.Log = this.Appendlog;
                    logic.ProcessInitSet = ProInitSet;
                    logic.ProcessReSet = ProReSet;
                    logic.ProcessSet = ProSet;
                    logic.ContinueOnError = chkContinue.Checked;
                    logic.DeleteFirst = chkDel.Checked;
                    logic.Targets = tableNames;
                    if (!System.IO.Directory.Exists(txtServer.Text))
                        System.IO.Directory.CreateDirectory(txtServer.Text);
                    cts = new CancellationTokenSource();
                    SetButtonEnable(this.btnCancel, true);
                    //btnCancel.Enabled = true;
                    await logic.Run(cts.Token);
                    this.Appendlog("任务结束!", Color.DarkBlue);

                }
                catch (OperationCanceledException cex)
                {
                    this.Appendlog(cex.Message, Color.DarkRed);
                }
                catch (Exception ex)
                {

                    this.Appendlog(ex.Message + "\n" + ex.StackTrace, Color.DarkRed);
                }
                finally
                {
                    SetButtonEnable(this.btnCancel, false);
                    fileWatcher.EnableRaisingEvents = true;
                    cts = null;
                    try
                    {
                        excelfile.Close();
                    }
                    catch(Exception ex1)
                    {
                        this.Appendlog(ex1.Message, Color.DarkOrange);
                    }
                    try
                    {
                        connection.Close();
                    }
                    catch (Exception ex2)
                    {
                        this.Appendlog(ex2.Message, Color.DarkOrange);
                    }

                    //if (book != null)
                    //{
                    //    book.Close(false);
                    //    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    //    book = null;
                    //}

                    //app.Workbooks.Close();
                    //app.Quit();
                    SetButtonEnable(this.btnCreator, true); 
                }



            });
        }


        private void cmbAuthType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cmbAuthType.SelectedIndex == 0)
            {
                this.txtPass.Text = string.Empty;
                this.txtPass.Enabled = false;
                this.txtUser.Text = Environment.UserDomainName + @"\" + Environment.UserName;
                this.txtUser.Enabled = false;
            }
            else
            {
                this.txtPass.Enabled = true;
                this.txtUser.Enabled = true;
                this.txtUser.Text = "sa";
            }
        }
        public void ProInitSet(int max, string txt)
        {
            this.BeginInvoke((ProcessInitSet)delegate(int m, string t)
            {
                this.pgbProcess.Maximum = m;
                this.Text = txt;


            }, max, txt);
        }
        public void ProSet(int value, string txt = null)
        {
            this.BeginInvoke((ProcessSet)delegate(int v, string t)
            {
                this.pgbProcess.Value = v;
                if (t != null)
                    this.Text = t;


            }, value, txt);
        }
        public void ProReSet()
        {
            this.BeginInvoke((ProcessReSet)delegate()
            {
                this.pgbProcess.Value = 0;

                this.Text = "准备接收新的任务";


            });
        }

        public void Appendlog(string message = null, Color? fontcolor = null)
        {
            this.Invoke((Proxy)delegate(string msg, Color? color)
            {

                if (msg == null)
                {
                    rtxtOutput.Clear();
                    return;
                }

                if (color == null)
                    color = Color.Black;
                rtxtOutput.SelectionColor = color.Value;
                rtxtOutput.AppendText(msg);
                rtxtOutput.AppendText("\n");
                //rtxtOutput.Focus();//获取焦点
                ////rtxtOutput.Select(rtxtOutput.TextLength - message.Length - 1, message.Length);//光标定位到文本最后

                //rtxtOutput.Select(rtxtOutput.TextLength, 0);//光标定位到文本最后
                //rtxtOutput.ScrollToCaret();//滚动到光标处

            }, message, fontcolor);

        }
        //private void MainForm_Load(object sender, EventArgs e)
        //{
        //    //SELECT value FROM 
        //    //sys.fn_listextendedproperty(N'MS_Description',N'SCHEMA',DEFAULT,N'TABLE',N'table2',N'COLUMN',N'a2') a WHERE a.name='MS_Description'
        //}

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (cts != null)
                cts.Cancel();
        }

        private void btnSample_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(txtServer.Text))
            {
                this.Appendlog("请输入数据库服务器地址.", Color.DarkRed);
                txtServer.Focus();
                return;
            }
            if (string.IsNullOrWhiteSpace(txtDatabase.Text))
            {
                this.Appendlog("请输入数据库名称.", Color.DarkRed);
                txtDatabase.Focus();
                return;
            }
            if (cmbAuthType.SelectedIndex != 0)
            {
                if (string.IsNullOrWhiteSpace(txtUser.Text))
                {
                    this.Appendlog("请输入用户名.", Color.DarkRed);
                    txtUser.Focus();
                    return;
                }
                if (string.IsNullOrWhiteSpace(txtPass.Text))
                {
                    this.Appendlog("请输入密码.", Color.DarkRed);
                    txtPass.Focus();
                    return;
                }
            }
            ObjTableForm form = new ObjTableForm();
            form.Tag = new  {
                ServerName=txtServer.Text,
                Db=txtDatabase.Text,
                User=txtUser.Text,
                Pass=txtPass.Text,
                AuthType=cmbAuthType.SelectedIndex
            
            
            };
            form.Show(this);



        }
        protected override void OnGotFocus(EventArgs e)
        {
            base.OnGotFocus(e);
            this.selectedtable.Hide();
        }
        private void MaiForm_LocationChanged(object sender, EventArgs e)
        {
            //Left = this.Left;
            //Top = this.Top + this.Height;
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            
        }

        private void showSelect(object o)
        {
            this.BeginInvoke((TimerCallback)delegate(object state)
            {
                //this.selectedtable.Tag = this;
                this.selectedtable.Opacity = 0d;
                this.selectedtable.SetSelectedItems(tableNames);
                this.selectedtable.Show();
                this.selectedtable.TopMost = true;
                this.selectedtable.TopMost = false;
                //this.selectedtable.TopLevel = true;
                Point position = (state as Point?).Value;
                this.selectedtable.Left = position.X;
                this.selectedtable.Top = position.Y;
                this.selectedtable.Opacity = 0.8d;
                //this.Text = ""+this.selectedtable.Left +";"+ this.selectedtable.Top;

            },o);
            showDelay.Dispose();
            showDelay = null;
        }
        private void hideSelect(object o)
        {
            this.BeginInvoke((TimerCallback)delegate(object state)
            {
                //this.selectedtable.Tag = null;
                this.selectedtable.Hide();
            }, o);
            hideDelay.Dispose();
            hideDelay = null;
        }
        private void txtExcelFile_MouseEnter(object sender, EventArgs e)
        {
            Point p = new Point();
            p.X=Cursor.Position.X;
            p.Y=Cursor.Position.Y;
            if (this.tableNames == null)
                return;
            if (showDelay == null)
            {
                showDelay = new System.Threading.Timer(showSelect, p, 500, Timeout.Infinite);
            }
            if (hideDelay != null)
            {
                hideDelay.Dispose();
                hideDelay = null;
            }

            
        }

        public void StopHide()
        {
            if (hideDelay != null)
            {
                hideDelay.Dispose();
                hideDelay = null;
            }
        }

        public void StartHide()
        {
            if (showDelay != null)
            {
                showDelay.Dispose();
                showDelay = null;
            }
            if (hideDelay == null)
            {
                hideDelay = new System.Threading.Timer(hideSelect, null, 500, Timeout.Infinite);  
            }

        }
        private void txtExcelFile_MouseLeave(object sender, EventArgs e)
        {

            StartHide();
            //this.selectedtable.Hide();
        }




    }
}
